<?php

namespace App\Http\Controllers\Api\Openapi;

use App\Models\Child;
use App\Models\File;
use App\Models\History;
use App\Http\Controllers\Controller;
use App\Models\Snapshot;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Symfony\Component\Process\Process;
use Illuminate\Support\Facades\DB;

class DataScreenController extends Controller
{

    private $key = 'zhongjiao';

    /**
     * 训练合计数量
     *
     * @param Request $request
     * @return mixed
     */
    public function historyCount(Request $request)
    {

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key){
            return response()->json(['status'=>300, 'msg'=>'数据为空']);
        }

        $historyCount = History::count();

        return response()->json(['status'=>200, 'data'=>['result'=>$historyCount]]);
    }

    /**
     * 训练最多的前15个项目
     *
     * @param Request $request
     * @return mixed
     */

    public function top15(Request $request){

        $data = $request->only(['last_id', 'token']);

        if ($data['token'] !== $this->key){
            return response()->json(['status'=>300, 'msg'=>'数据为空']);
        }

        $historyCount = DB::select("SELECT num, b.name FROM ( SELECT COUNT(id) as num, game_id FROM histories GROUP by game_id ORDER by num DESC ) as temp LEFT JOIN games b ON temp.game_id = b.id LIMIT 15");

        return response()->json(['status'=>200, 'data'=>['result'=>$historyCount]]);
    }

    /**
     * 机构学生数量
     *
     * @param Request $request
     * @return mixed
     */

    public function agencyStudentNum(Request $request){

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key){
            return response()->json(['status'=>300, 'msg'=>'数据为空']);
        }

        $agencyStudent = DB::select("SELECT num, b.name FROM ( SELECT COUNT(id) as num, agency_id FROM children GROUP by agency_id ORDER by num DESC ) as temp LEFT JOIN agencies b ON temp.agency_id = b.id LIMIT 15");

        return response()->json(['status'=>200, 'data'=>['result'=>$agencyStudent]]);
    }

    /**
     * 按省份统计学生数量
     *
     * @param Request $request
     * @return mixed
     */

    public function studentNum(Request $request){

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key){
            return response()->json(['status'=>300, 'msg'=>'数据为空']);
        }

        $agencyStudent = DB::select("SELECT num, b.name,b.province FROM ( SELECT COUNT(id) as num, agency_id FROM children GROUP by agency_id ORDER by num DESC ) as temp LEFT JOIN agencies b ON temp.agency_id = b.id LIMIT 15");

        $result= [];
        foreach ($agencyStudent as $item){
            if (isset($request[$item['province']])){
                $result[$item['province']] += $item['num'];
            }else{
                $result[$item['province']] = $item['num'];
            }
        }

        return response()->json(['status'=>200, 'data'=>['result'=>$result]]);
    }

    /**
     * 按训练方向分布
     *
     * @param Request $request
     * @return mixed
     */

    public function categoryNum(Request $request){

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key){
            return response()->json(['status'=>300, 'msg'=>'数据为空']);
        }

        $sql = 'select count( case when a.category_id = 1 then 1 else null end ) as "紧张感", count( case when a.category_id = 2 then 1 else null end ) as "稳定性", count( case when a.category_id = 3 then 1 else null end ) as "广度", count( case when a.category_id = 4 then 1 else null end ) as "转移", count( case when a.category_id = 5 then 1 else null end ) as "分配", b.name as grade_name from histories a left join grades b on a.grade_id = b.id group by a.grade_id';

        $categoryNum = DB::select( $sql );

        return response()->json(['status'=>200, 'data'=>['result' => $categoryNum]]);
    }

    /**
     * 按训练项目统计
     *
     * @param Request $request
     * @return mixed
     */

    public function historyNum(Request $request){

        $data = $request->only(['token']);

        if ($data['token'] !== $this->key){
            return response()->json(['status'=>300, 'msg'=>'数据为空']);
        }

        $sql='select a.game_id, count(a.id) as num, a.result, b.name as game_name from histories a left join games b on a.game_id = b.id group by a.game_id, a.result order by game_id';

        $categoryNum = DB::select( $sql );

        return response()->json(['status'=>200, 'data'=>['result' => $categoryNum]]);
    }

}
